package ru.osu.acm.common.industrial;

import ru.osu.acm.common.entities.Contest;

import java.util.ArrayList;
import java.util.Date;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * User: aleksey
 * Date: Sep 22, 2009
 * Time: 3:02:17 PM
 */
public class ContestFactory extends Factory {
    public static Contest getContest(int contestId, Connection con, boolean closeConnection) {
        Contest contest = null;

        try {
            PreparedStatement statement = con.prepareStatement("select * from contest where id = ?");
            statement.setInt(1, contestId);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                contest = new Contest(
                        resultSet.getInt("id"),
                        resultSet.getString("contestname").trim(),
                        resultSet.getTimestamp("startdate"),
                        resultSet.getTimestamp("stopdate"),
                        resultSet.getString("contesttype").trim());
            }
            resultSet.close();
            statement.close();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

        closeConnection(con, closeConnection);
        return contest;
    }

    public static int getContestCount(Connection con, boolean closeConnection) {
        int count = 0;

        try {
            PreparedStatement statement = con.prepareStatement("select count(*) as contestcount from contest");
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                count = resultSet.getInt("contestcount");
            }
            resultSet.close();
            statement.close();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

        closeConnection(con, closeConnection);
        return count;
    }

    public static ArrayList<Contest> getAllContests(Connection con, boolean closeConnection) {
        ArrayList<Contest> contests = new ArrayList<Contest>();

        try {
            PreparedStatement statement = con.prepareStatement("select * from contest order by id");
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                contests.add(new Contest(
                        resultSet.getInt("id"),
                        resultSet.getString("contestname").trim(),
                        resultSet.getTimestamp("startdate"),
                        resultSet.getTimestamp("stopdate"),
                        resultSet.getString("contesttype").trim()));
            }
            resultSet.close();
            statement.close();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

        closeConnection(con, closeConnection);
        return contests;
    }

    public static ArrayList<Contest> getUserContests(int userId, Connection con, boolean closeConnection) {
        ArrayList<Contest> contests = new ArrayList<Contest>();

        try {
            PreparedStatement statement = con.prepareStatement("select * from contestperson where person = ? order by contest asc");
            statement.setInt(1, userId);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Contest contest = getContest(resultSet.getInt("contest"), con, false);
                contests.add(contest);
            }
            resultSet.close();
            statement.close();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

        closeConnection(con, closeConnection);
        return contests;
    }

    public static ArrayList<Contest> getTeamContests(int teamId, Connection con, boolean closeConnection) {
        ArrayList<Contest> contests = new ArrayList<Contest>();

        try {
            PreparedStatement statement = con.prepareStatement("select * from contestteam where team = ? order by contest asc");
            statement.setInt(1, teamId);
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Contest contest = getContest(resultSet.getInt("contest"), con, false);
                contests.add(contest);
            }
            resultSet.close();
            statement.close();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

        closeConnection(con, closeConnection);
        return contests;
    }

    public static void updateContest(Contest contest, Connection con, boolean closeConnection) {
        if (contest.getId() == -1) {
            contest.setId(getNextId("contest", con, false));

            try {
                PreparedStatement statement = con.prepareStatement("insert into contest (id, contestname, startdate, stopdate, contesttype) values (?, ?, ?, ?, ?)");
                statement.setInt(1, contest.getId());
                statement.setString(2, contest.getName());
                statement.setTimestamp(3, contest.getStartDate() == null ? new java.sql.Timestamp(new Date().getTime()) : new java.sql.Timestamp(contest.getStartDate().getTime()));
                statement.setTimestamp(4, contest.getStopDate() == null ? new java.sql.Timestamp(new Date().getTime()) : new java.sql.Timestamp(contest.getStopDate().getTime()));
                statement.setString(5, contest.getType());
                statement.executeUpdate();
                statement.close();
            }
            catch (SQLException e) {
                e.printStackTrace();
            }
            
            closeConnection(con, closeConnection);
            return;
        }

        try {
            PreparedStatement statement = con.prepareStatement("update contest set contestname = ?, startdate = ?, stopdate = ?, contesttype = ? where id = ?");
            statement.setString(1, contest.getName());
            statement.setTimestamp(2, contest.getStartDate() == null ? new java.sql.Timestamp(new Date().getTime()) : new java.sql.Timestamp(contest.getStartDate().getTime()));
            statement.setTimestamp(3, contest.getStopDate() == null ? new java.sql.Timestamp(new Date().getTime()) : new java.sql.Timestamp(contest.getStopDate().getTime()));
            statement.setString(4, contest.getType());
            statement.setInt(5, contest.getId());
            statement.executeUpdate();
            statement.close();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

        closeConnection(con, closeConnection);
    }

    public static int getNextProblemIndex(int contestId, Connection con, boolean closeConnection) {
        int index = 0;

        try {
            PreparedStatement statement = con.prepareStatement("select problemindex from contestproblem where contest = ? order by problemindex desc limit 1");
            statement.setInt(1, contestId);
            ResultSet resultSet = statement.executeQuery();
            if (resultSet.next()) {
                index = resultSet.getInt("problemindex") + 1;
            }
            resultSet.close();
            statement.close();
        }
        catch (SQLException e) {
            e.printStackTrace();
        }

        closeConnection(con, closeConnection);
        return index;
    }

    public static Contest getNewContest() {
        return new Contest(-1, "", null, null);
    }
}
